Programming Microsoft Visual Basic .NET/Programming Microsoft Visual Basic .NET (Microsoft Press)(X08-78517)(2002).bin/setup/vbnet/21 adonet disconnected/adonetdisconnected/conflictform.vb

Text File | 2002-03-16 | 37.2 KB | 875 lines

Imports System.Data
Imports System.Data.OleDb
Imports System.Data.SqlClient

Public Class ConflictForm
    Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "

    Public Sub New()
        MyBase.New()

        'This call is required by the Windows Form Designer.
        InitializeComponent()

        'Add any initialization after the InitializeComponent() call

    End Sub

    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub
    Friend WithEvents DataGrid1 As System.Windows.Forms.DataGrid

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.Container

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.
    'Do not modify it using the code editor.
    Friend WithEvents btnShowRows As System.Windows.Forms.Button
    Friend WithEvents btnRunTransaction As System.Windows.Forms.Button
    Friend WithEvents btnResync As System.Windows.Forms.Button
    Friend WithEvents btnRowByRowResync As System.Windows.Forms.Button
    Friend WithEvents btnUseRowUpdating As System.Windows.Forms.Button
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.DataGrid1 = New System.Windows.Forms.DataGrid()
        Me.btnShowRows = New System.Windows.Forms.Button()
        Me.btnRunTransaction = New System.Windows.Forms.Button()
        Me.btnResync = New System.Windows.Forms.Button()
        Me.btnRowByRowResync = New System.Windows.Forms.Button()
        Me.btnUseRowUpdating = New System.Windows.Forms.Button()
        CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.SuspendLayout()
        '
        'DataGrid1
        '
        Me.DataGrid1.Anchor = (((System.Windows.Forms.AnchorStyles.Top Or System.Windows.Forms.AnchorStyles.Bottom) _
                    Or System.Windows.Forms.AnchorStyles.Left) _
                    Or System.Windows.Forms.AnchorStyles.Right)
        Me.DataGrid1.DataMember = ""
        Me.DataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText
        Me.DataGrid1.Location = New System.Drawing.Point(152, 16)
        Me.DataGrid1.Name = "DataGrid1"
        Me.DataGrid1.Size = New System.Drawing.Size(512, 328)
        Me.DataGrid1.TabIndex = 0
        '
        'btnShowRows
        '
        Me.btnShowRows.Location = New System.Drawing.Point(8, 16)
        Me.btnShowRows.Name = "btnShowRows"
        Me.btnShowRows.Size = New System.Drawing.Size(128, 40)
        Me.btnShowRows.TabIndex = 2
        Me.btnShowRows.Text = "Show Conflicting Rows"
        '
        'btnRunTransaction
        '
        Me.btnRunTransaction.Location = New System.Drawing.Point(8, 160)
        Me.btnRunTransaction.Name = "btnRunTransaction"
        Me.btnRunTransaction.Size = New System.Drawing.Size(128, 40)
        Me.btnRunTransaction.TabIndex = 3
        Me.btnRunTransaction.Text = "Run in Transaction"
        '
        'btnResync
        '
        Me.btnResync.Location = New System.Drawing.Point(8, 64)
        Me.btnResync.Name = "btnResync"
        Me.btnResync.Size = New System.Drawing.Size(128, 40)
        Me.btnResync.TabIndex = 4
        Me.btnResync.Text = "Resync conflicting rows"
        '
        'btnRowByRowResync
        '
        Me.btnRowByRowResync.Location = New System.Drawing.Point(8, 112)
        Me.btnRowByRowResync.Name = "btnRowByRowResync"
        Me.btnRowByRowResync.Size = New System.Drawing.Size(128, 40)
        Me.btnRowByRowResync.TabIndex = 5
        Me.btnRowByRowResync.Text = "Row-by-row resync"
        '
        'btnUseRowUpdating
        '
        Me.btnUseRowUpdating.Location = New System.Drawing.Point(8, 208)
        Me.btnUseRowUpdating.Name = "btnUseRowUpdating"
        Me.btnUseRowUpdating.Size = New System.Drawing.Size(128, 40)
        Me.btnUseRowUpdating.TabIndex = 6
        Me.btnUseRowUpdating.Text = "RowUpdating Event"
        '
        'ConflictForm
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(7, 17)
        Me.ClientSize = New System.Drawing.Size(672, 349)
        Me.Controls.AddRange(New System.Windows.Forms.Control() {Me.btnUseRowUpdating, Me.btnRowByRowResync, Me.btnResync, Me.btnRunTransaction, Me.btnShowRows, Me.DataGrid1})
        Me.Font = New System.Drawing.Font("Microsoft Sans Serif", 11.0!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.Name = "ConflictForm"
        Me.Text = "Conflict Detection"
        CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)

    End Sub

#End Region

    ' This form only works with SQL Server's Pubs database.
    ' ensure that only Const is -1
#Const OLEDBPUBS = -1
#Const SQLPUBS = 0

    Dim ds As New DataSet()

    ' show the Publishers table

    Private Sub btnShowRows_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnShowRows.Click
        Dim sql As String = "SELECT * FROM Publishers"

#If OLEDBPUBS Then
        Dim cn As New OleDbConnection(OledbPubsConnString)
        Dim da As New OleDbDataAdapter(sql, cn)
        Dim cmd As OleDbCommand
#ElseIf SQLPUBS Then
        Dim cn As New SqlConnection(SqlPubsConnString)
        Dim da As New SqlDataAdapter(sql, cn)
        dim cmd as SqlCommand
#End If

        cn.Open()
        If ds.Tables.Contains("Publishers") Then ds.Tables("Publishers").Clear()
        da.FillSchema(ds, SchemaType.Source, "Publishers")
        da.Fill(ds, "Publishers")

        DataGrid1.DataSource = ds.Tables("Publishers")

#If OLEDBPUBS Then
        Dim cmdBuilder As New OleDbCommandBuilder(da)
        Dim par As OleDbParameter
#ElseIf SQLPUBS Then
        Dim cmdBuilder As New SqlCommandBuilder(da)
        Dim par As SqlParameter
#End If

        ' generate the three commands
        da.DeleteCommand = cmdBuilder.GetDeleteCommand
        da.InsertCommand = cmdBuilder.GetInsertCommand
        da.UpdateCommand = cmdBuilder.GetUpdateCommand

        ' Display info on auto-generated commands
        Debug.WriteLine(da.InsertCommand.CommandText)
        Debug.WriteLine(da.UpdateCommand.CommandText)
        Debug.WriteLine(da.DeleteCommand.CommandText)
        For Each par In da.UpdateCommand.Parameters
            Debug.WriteLine(par.ParameterName & " => " & par.SourceColumn & "(" & par.SourceVersion.ToString & ")")
        Next

        ' Send a command that modifies the pub_name of first row
        cmd = cn.CreateCommand
        cmd.CommandText = "UPDATE Publishers SET pub_name ='" & ds.Tables("Publishers").Rows(0)("pub_name").ToString & " (mod)' WHERE Pub_id='" _
            & ds.Tables("Publishers").Rows(0)("pub_id").ToString & "'"
        cmd.ExecuteNonQuery()

        ' modify the local copy of the data.
        With ds.Tables("Publishers")
            ' Modify the first record (this fails because this record has changed)
            .Rows(0)("pub_name") = .Rows(0)("pub_name").ToString & " (new)"
            ' Add a new record (this fails because there is already a record with this key)
            Dim newdr As DataRow = .NewRow
            newdr("pub_id") = "9999"
            newdr("pub_name") = "Vb2TheMax"
            newdr("city") = "Bari"
            newdr("country") = "Italy"
            .Rows.Add(newdr)
            ' delete the second row. (this fails because of RI constraints)
            .Rows(1).Delete()
        End With

        ' Ensure that all conflict are ignored, so that the application
        ' attempts to update all rows.
        da.ContinueUpdateOnError = True
        da.Update(ds, "Publishers")
        cn.Close()

        ' exit if the attempt was successfull
        If Not ds.HasChanges Then Exit Sub

        ' Not all rows were updated successfully.
        Dim dt As DataTable = ds.Tables("Publishers")

        ' Here's a simple way to evaluate the number of conflicting rows.
        Dim rowCount As Integer = dt.GetChanges().Rows.Count

        ' This block of code shows how to mark conflicting rows
        ' without attempting to resync them with the data source.

        ' Mark all conflicting row with proper error message.
        Dim dr As DataRow
        For Each dr In dt.Rows
            If dr.RowState = DataRowState.Added Then
                dr.RowError = "Failed INSERT operation"
            ElseIf dr.RowState = DataRowState.Modified Then
                dr.RowError = "Failed UPDATE operation"
            ElseIf dr.RowState = DataRowState.Deleted Then
                dr.RowError = "Failed DELETE operation"
                ' Undelete this record, otherwise it wouldn't show in the table.
                dr.RejectChanges()
            End If
        Next
    End Sub

    ' demonstrates how to run updates inside a transaction

    Private Sub btnRunTransaction_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRunTransaction.Click
        Dim sql As String = "SELECT * FROM Publishers"

#If OLEDBPUBS Then
        Dim cn As New OleDbConnection(OledbPubsConnString)
        Dim da As New OleDbDataAdapter(sql, cn)
        Dim cmd As OleDbCommand
#ElseIf SQLPUBS Then
        Dim cn As New SqlConnection(SqlPubsConnString)
        Dim da As New SqlDataAdapter(sql, cn)
        dim cmd as SqlCommand
#End If

        cn.Open()
        If ds.Tables.Contains("Publishers") Then ds.Tables("Publishers").Clear()
        'da.FillSchema(ds, SchemaType.Source, "Publishers")
        da.Fill(ds, "Publishers")

        DataGrid1.DataSource = ds.Tables("Publishers")

#If OLEDBPUBS Then
        Dim cmdBuilder As New OleDbCommandBuilder(da)
        Dim par As OleDbParameter

#ElseIf SQLPUBS Then
        Dim cmdBuilder As New SqlCommandBuilder(da)
        Dim par As SqlParameter
#End If

        ' generate the three default command
        da.DeleteCommand = cmdBuilder.GetDeleteCommand
        da.InsertCommand = cmdBuilder.GetInsertCommand
        da.UpdateCommand = cmdBuilder.GetUpdateCommand

        ' Send a command that modifies the pub_name of first row
        cmd = cn.CreateCommand
        cmd.CommandText = "UPDATE Publishers SET pub_name ='" & ds.Tables("Publishers").Rows(0)("pub_name").ToString & " (mod)' WHERE Pub_id='" _
            & ds.Tables("Publishers").Rows(0)("pub_id").ToString & "'"
        cmd.ExecuteNonQuery()

        ' modify the local copy of the data.
        With ds.Tables("Publishers")
            ' Modify the first record (this fails because this record has changed)
            .Rows(0)("pub_name") = .Rows(0)("pub_name").ToString & " (new)"
            ' Add a new record (this fails because there is already a record with this key)
            Dim newdr As DataRow = .NewRow
            newdr("pub_id") = "9999"
            newdr("pub_name") = "Vb2TheMax"
            newdr("city") = "Bari"
            newdr("country") = "Italy"
            .Rows.Add(newdr)
            ' delete the second row. (this fails because of RI constraints)
            .Rows(1).Delete()
        End With

        ' Run the Update method inside a transaction
        Dim tr As OleDbTransaction
        tr = cn.BeginTransaction()

        ' Enroll all the update commands inside the same transaction.
        da.UpdateCommand.Transaction = tr
        da.DeleteCommand.Transaction = tr
        da.InsertCommand.Transaction = tr

        ' Send changes them to the database.
        Try
            da.Update(ds, "Publishers")
            ' commit if everything was OK
            tr.Commit()
        Catch ex As Exception
            ' Rollback the transaction, if there is one.
            If Not (tr Is Nothing) Then
                tr.Rollback()
                ' Let the user know that there was a problem.
                MessageBox.Show(ex.Message, "Update error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End If
        End Try
        cn.Close()
    End Sub

    ' resync with the data source

    Private Sub btnResync_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnResync.Click
        Dim sql As String = "SELECT * FROM Publishers"

#If OLEDBPUBS Then
        Dim cn As New OleDbConnection(OledbPubsConnString)
        Dim da As New OleDbDataAdapter(sql, cn)
        Dim cmd As OleDbCommand
#ElseIf SQLPUBS Then
        Dim cn As New SqlConnection(SqlPubsConnString)
        Dim da As New SqlDataAdapter(sql, cn)
        dim cmd as SqlCommand
#End If

        cn.Open()
        If ds.Tables.Contains("Publishers") Then ds.Tables("Publishers").Clear()
        'da.FillSchema(ds, SchemaType.Source, "Publishers")
        da.Fill(ds, "Publishers")

        DataGrid1.DataSource = ds.Tables("Publishers")

#If OLEDBPUBS Then
        Dim cmdBuilder As New OleDbCommandBuilder(da)
        Dim par As OleDbParameter
#ElseIf SQLPUBS Then
        Dim cmdBuilder As New SqlCommandBuilder(da)
        Dim par As SqlParameter
#End If

        da.DeleteCommand = cmdBuilder.GetDeleteCommand
        da.InsertCommand = cmdBuilder.GetInsertCommand
        da.UpdateCommand = cmdBuilder.GetUpdateCommand

        ' Send a command that modifies the pub_name of first row
        cmd = cn.CreateCommand
        cmd.CommandText = "UPDATE Publishers SET pub_name ='" & ds.Tables("Publishers").Rows(0)("pub_name").ToString & " (mod)' WHERE Pub_id='" _
            & ds.Tables("Publishers").Rows(0)("pub_id").ToString & "'"
        cmd.ExecuteNonQuery()

        ' modify the local copy of the data.
        With ds.Tables("Publishers")
            ' Modify the first record (this fails because this record has changed)
            .Rows(0)("pub_name") = .Rows(0)("pub_name").ToString & " (new)"
            ' Add a new record (this fails because there is already a record with this key)
            Dim newdr As DataRow = .NewRow
            newdr("pub_id") = "9999"
            newdr("pub_name") = "Vb2TheMax"
            newdr("city") = "Bari"
            newdr("country") = "Italy"
            .Rows.Add(newdr)
            ' delete the second row. (this fails because of RI constraints)
            .Rows(1).Delete()
        End With

        ' Ensure that all conflict are ignored, so that the application
        ' attempts to update all rows.
        da.ContinueUpdateOnError = True
        da.Update(ds, "Publishers")
        cn.Close()

        ' exit if the attempt was successfull
        If Not ds.HasChanges Then Exit Sub

        ' Not all rows were updated successfully.
        Dim dt As DataTable = ds.Tables("Publishers")

        ' This block of code shows how to mark conflicting rows and
        ' doing a resynch with the data source.

        ' Keeping key column name in a variable helps make this code reusable.
        Dim keyName As String = "pub_id"

        ' Build the list of the key values for all these rows.
        Dim values As New System.Text.StringBuilder(1000)
        Dim keyValue As String
        Dim dr As DataRow

        For Each dr In dt.Rows
            ' Only consider modified rows.
            If dr.RowState <> DataRowState.Unchanged Then
                ' The key to be used depends on the row state.
                If dr.RowState = DataRowState.Added Then
                    ' Use the current key for inserted rows.
                    keyValue = dr(keyName, DataRowVersion.Current).ToString
                Else
                    ' Use the original key for deleted and modified rows.
                    keyValue = dr(keyName, DataRowVersion.Original).ToString
                End If
                ' Append to the list of keys
                If values.Length > 0 Then values.Append(",")
                values.Append("'")
                values.Append(keyValue)
                values.Append("'")
            End If
        Next

        ' Create a new SELECT with only these records, using the DataAdapter
        ' SELECT command as a template
        Dim sql2 As String = da.SelectCommand.CommandText
        ' Delete the WHERE clause, if there is one.
        Dim k As Integer = sql2.ToUpper.IndexOf(" WHERE ")
        If k > 0 Then sql2 = sql2.Substring(0, k - 1)
        ' Add the WHERE clause that contains the list of all key values.
        sql2 &= " WHERE " & keyName & " IN (" & values.ToString & ")"

        ' Read again only the conflicting rows.
#If OLEDBPUBS Then
        Dim da2 As New OleDbDataAdapter(sql2, cn)
#ElseIf SQLPUBS Then
        Dim da2 As New SqlDataAdapter(sql2, cn)
#End If
        ' Fill a new DataTable (it doesn't have to belong to the DataSet).
        Dim dt2 As New DataTable()
        da2.Fill(dt2)

        ' Loop on all the rows that failed to update.
        Dim dr2 As DataRow
        For Each dr In dt.Rows
            If dr.RowState <> DataRowState.Unchanged Then
                ' Mark the row with a proper error message, get the key to be
                ' used for searching in DT2.
                If dr.RowState = DataRowState.Added Then
                    dr.RowError = "Failed INSERT command"
                    keyValue = dr(keyName, DataRowVersion.Current).ToString
                ElseIf dr.RowState = DataRowState.Deleted Then
                    dr.RowError = "Failed DELETE command"
                    keyValue = dr(keyName, DataRowVersion.Original).ToString
                ElseIf dr.RowState = DataRowState.Modified Then
                    dr.RowError = "Failed UPDATE command"
                    keyValue = dr(keyName, DataRowVersion.Original).ToString
                End If

                ' Get the matching row in the new table.
                Dim rows() As DataRow = dt2.Select(keyName & "='" & keyValue & "'")
                If (rows Is Nothing) OrElse rows.Length = 0 Then
                    ' We can't find the conflicting row in the database.
                    dr.RowError &= " - Unable to resync with data source"
                    ' Check whether the user changed the primary key.
                    If dr.RowState <> DataRowState.Added AndAlso dr(keyName, DataRowVersion.Current).ToString <> dr(keyName, DataRowVersion.Original).ToString Then
                        ' This is a probable source of the conflict.
                        dr.SetColumnError(keyName, "Modified primary key")
                    End If

                Else
                    ' We have found the conflicting row in the database, so
                    ' we can compare values now in each column.
                    dr2 = rows(0)

                    Dim i As Integer
                    For i = 0 To dr.Table.Columns.Count - 1
                        ' The comparisons we do depend on the row state.
                        If dr.RowState = DataRowState.Added Then
                            ' For inserted rows we can compare current value with database value.
                            If dr(i).ToString <> dr2(i).ToString Then
                                ' Show the value now in database.
                                dr.SetColumnError(i, "Value in database = " & dr2(i).ToString)
                            End If
                        Else
                            ' For deleted and modified rows we can compare original value with database value.
                            If dr(i, DataRowVersion.Original).ToString <> dr2(i).ToString Then
                                Dim msg As String = ""
                                If dr(i, DataRowVersion.Original).ToString <> dr(i).ToString Then
                                    msg = "Original value = " & dr(i).ToString & ", "
                                End If
                                msg &= "Value in database = " & dr2(i).ToString
                                dr.SetColumnError(i, msg)
                            End If
                        End If
                    Next

                End If
            End If

            ' If this is a deleted row, reject changes to make it visible in table.
            If dr.RowState = DataRowState.Deleted Then
                dr.RejectChanges()
            End If
        Next
    End Sub

    ' demonstrate a row-by-row resync

    Private Sub btnRowByRowResync_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRowByRowResync.Click
        Dim sql As String = "SELECT * FROM Publishers"

#If OLEDBPUBS Then
        Dim cn As New OleDbConnection(OledbPubsConnString)
        Dim da As New OleDbDataAdapter(sql, cn)
        Dim cmd As OleDbCommand
#Else
        Dim cn As New SqlConnection(SqlPubsConnString)
        Dim da As New SqlDataAdapter(sql, cn)
        dim cmd as SqlCommand
#End If

        cn.Open()
        If ds.Tables.Contains("Publishers") Then ds.Tables("Publishers").Clear()
        'da.FillSchema(ds, SchemaType.Source, "Publishers")
        da.Fill(ds, "Publishers")

        DataGrid1.DataSource = ds.Tables("Publishers")

#If OLEDBPUBS Then
        Dim cmdBuilder As New OleDbCommandBuilder(da)
        Dim par As OleDbParameter

#ElseIf SQLPUBS Then
        Dim cmdBuilder As New SqlCommandBuilder(da)
        Dim par As SqlParameter
#End If

        da.DeleteCommand = cmdBuilder.GetDeleteCommand
        da.InsertCommand = cmdBuilder.GetInsertCommand
        da.UpdateCommand = cmdBuilder.GetUpdateCommand

        ' Send a command that modifies the pub_name of first row
        cmd = cn.CreateCommand
        cmd.CommandText = "UPDATE Publishers SET pub_name ='" & ds.Tables("Publishers").Rows(0)("pub_name").ToString & " (mod)' WHERE Pub_id='" _
            & ds.Tables("Publishers").Rows(0)("pub_id").ToString & "'"
        cmd.ExecuteNonQuery()

        ' modify the local copy of the data.
        With ds.Tables("Publishers")
            ' Modify the first record (this fails because this record has changed)
            .Rows(0)("pub_name") = .Rows(0)("pub_name").ToString & " (new)"
            ' Add a new record (this fails because there is already a record with this key)
            Dim newdr As DataRow = .NewRow
            newdr("pub_id") = "9999"
            newdr("pub_name") = "Vb2TheMax"
            newdr("city") = "Bari"
            newdr("country") = "Italy"
            .Rows.Add(newdr)
            ' delete the second row. (this fails because of RI constraints)
            .Rows(1).Delete()
        End With

        ' Prepare the Command that reads a single row.
        resyncCmd = GetCurrentRowCommand(da, ds.Tables("Publishers").Columns("pub_id"))

        ' Ensure that all conflict are ignored, so that the application
        ' attempts to update all rows.
        AddHandler da.RowUpdated, AddressOf OnRowUpdated_Resync
        da.Update(ds, "Publishers")
        cn.Close()
    End Sub

    ' Retrieve a single record from a table.
    Function GetCurrentRowCommand(ByVal da As OleDbDataAdapter, ByVal ParamArray keyColumns() As DataColumn) As OleDbCommand
        ' Get the SELECT statement in the DataAdapter
        Dim sql As String = da.SelectCommand.CommandText
        ' Truncate the statement just before the WHERE clause, if there is one.
        Dim i As Integer = sql.ToUpper.IndexOf("WHERE ")
        If i > 0 Then sql = sql.Substring(0, i - 1)

        ' Add the WHERE clause on all primary key fields.
        Dim dc As DataColumn
        Dim sb As New System.Text.StringBuilder(100)
        For Each dc In keyColumns
            If sb.Length > 0 Then sb.Append(" AND ")
            sb.Append("[")
            'sql.Append (tmap.ColumnMappings.GetByDataSetColumn(dc.ColumnName).SourceColumn
            sb.Append(dc.ColumnName)
            sb.Append("]=?")
        Next
        sql &= " WHERE " & sb.ToString

        ' Create the command object on the same connection
#If OLEDBPUBS Then
        Dim cmd As New OleDbCommand(sql, CType(da.SelectCommand.Connection, OleDbConnection))
#ElseIf SQLPUBS Then
        Dim cmd As New SqlCommand(sql, CType(da.SelectCommand.Connection, SqlConnection))
#End If
        ' create the collection of parameters.
        For Each dc In keyColumns
            cmd.Parameters.Add(dc.ColumnName, dc.DataType)
        Next

        ' Return the command
        Return cmd
    End Function


#If OLEDBPUBS Then
    ' This holds the command that does the row-by-row resync
    Dim resyncCmd As OleDbCommand

    Sub OnRowUpdated_Resync(ByVal sender As Object, ByVal args As OleDbRowUpdatedEventArgs)
#Else

    Dim resyncCmd As SqlCommand

    Sub OnRowUpdated_Resync(ByVal sender As Object, ByVal args As SqlRowUpdatedEventArgs)
#End If
        If args.Status <> UpdateStatus.ErrorsOccurred Then
            ' Update was ok.
        ElseIf Not TypeOf args.Errors Is DBConcurrencyException Then
            ' An error occorred
            args.Row.RowError = "Failed to update: " & args.Errors.Message
            ' Continue the update operation anyway.
            args.Status = UpdateStatus.Continue
        Else
            ' An update conflict occurred.
            Dim dr As DataRow = args.Row
            Dim keyValue As String
            Dim keyName As String = "pub_id"

            Select Case args.StatementType
                Case StatementType.Insert
                    dr.RowError = "Conflict on an INSERT operation"
                    keyValue = dr(keyName, DataRowVersion.Current).ToString
                Case StatementType.Delete
                    dr.RowError = "Conflict on a DELETE operation"
                    keyValue = dr(keyName, DataRowVersion.Original).ToString
                Case StatementType.Update
                    dr.RowError = "Conflict on an UPDATE operation"
                    keyValue = dr(keyName, DataRowVersion.Original).ToString
            End Select

            ' read the current row - use the original key value, though.
            ' (This is necessary otherwise you get an error if the row has been deleted)
            resyncCmd.Parameters(keyName).Value = keyValue
            ' we use IDataReader for db independence
            Dim dre As IDataReader = resyncCmd.ExecuteReader(CommandBehavior.SingleRow)

            ' Advanced to first record, and remember whether there is a record.
            Dim recordFound As Boolean = dre.Read

            If recordFound And args.StatementType = StatementType.Insert Then
                ' We attempted an insert on a record that is already there.
                dr.RowError &= "- There is already a record with key = " & keyValue
            ElseIf Not recordFound And args.StatementType <> StatementType.Insert Then
                ' We attempted to update/delete a record that isn't there any longer
                dr.RowError &= "Can't find a matching record with key = " & keyValue
            Else
                ' The operation failed for some other reason.
                ' In this demo we just print information on all changed columns.

                ' At the end of the loop this variable is 0 only if the current value
                ' and the database value are the same for all conflicting columns
                Dim nonMatchingColumns As Integer = 0

                Dim i As Integer
                For i = 0 To dre.FieldCount - 1
                    Dim dbValue, origValue, currValue As Object
                    ' Get current value in database, if there is a matching record.
                    If recordFound Then
                        dbValue = dre(i)
                    End If
                    ' Get original value, if not an Insert operation.
                    If args.StatementType <> StatementType.Insert Then
                        origValue = args.Row(i, DataRowVersion.Original)
                    End If
                    ' Get the current value, if not a Delete operation.
                    If args.StatementType <> StatementType.Delete Then
                        currValue = args.Row(i, DataRowVersion.Current)
                    End If
                    ' Decide whether this field might be a potential source for a conflict.
                    Dim conflicting As Boolean = False

                    If Not recordFound Then
                        ' If couldn't find the record, any modified column can be considered
                        ' as a potential cause for conflict.
                        If Not (origValue Is Nothing) AndAlso Not (currValue Is Nothing) AndAlso (origValue.ToString <> currValue.ToString) Then
                            conflicting = True
                        End If
                    Else
                        ' If the record was found, but the original and the database values
                        ' differ, then we've found a cause for the conflict.
                        If Not (origValue Is Nothing) AndAlso (dbValue.ToString <> origValue.ToString) Then
                            conflicting = True
                            If Not (currValue Is Nothing) AndAlso (dbValue.ToString <> currValue.ToString) Then
                                ' We've found a column for which the database and current
                                ' values don't match.
                                nonMatchingColumns += 1
                            End If
                        End If
                    End If

                    If conflicting Then
                        ' Display field name and all related values.
                        Dim msg As String = ""
                        If Not (origValue Is Nothing) Then
                            msg = "Original value = " & origValue.ToString & ","
                        End If
                        If Not (dbValue Is Nothing) Then
                            msg &= "Value in database =" & dbValue.ToString
                        End If
                        dr.SetColumnError(i, msg)
                    End If
                Next

                ' If we've found a record in the database and all the values in the
                ' database match the current values, it means that we can consider
                ' this record as successfully updated, because another user had
                ' inserted exactly the same values that this user wanted to change.
                If recordFound And nonMatchingColumns = 0 Then
                    dr.AcceptChanges()
                    dr.ClearErrors()
                End If
            End If

            ' Close the DataReader.
            dre.Close()

            ' In all cases, swallow the exception and continue.
            args.Status = UpdateStatus.Continue

        End If
    End Sub

    ' demonstrate the RowUpdating event

    Private Sub btnUseRowUpdating_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUseRowUpdating.Click
        Dim sql As String = "SELECT * FROM Publishers"

#If OLEDBPUBS Then
        Dim cn As New OleDbConnection(OledbPubsConnString)
        Dim da As New OleDbDataAdapter(sql, cn)
        Dim cmd As OleDbCommand
#ElseIf SQLPUBS Then
        Dim cn As New SqlConnection(SqlPubsConnString)
        Dim da As New SqlDataAdapter(sql, cn)
        dim cmd as SqlCommand
#End If

        cn.Open()
        If ds.Tables.Contains("Publishers") Then ds.Tables("Publishers").Clear()
        'da.FillSchema(ds, SchemaType.Source, "Publishers")
        da.Fill(ds, "Publishers")

        DataGrid1.DataSource = ds.Tables("Publishers")

#If OLEDBPUBS Then
        Dim cmdBuilder As New OleDbCommandBuilder(da)
        Dim par As OleDbParameter

#ElseIf SQLPUBS Then
        Dim cmdBuilder As New SqlCommandBuilder(da)
        Dim par As SqlParameter
#End If

        da.DeleteCommand = cmdBuilder.GetDeleteCommand
        da.InsertCommand = cmdBuilder.GetInsertCommand
        da.UpdateCommand = cmdBuilder.GetUpdateCommand

        ' Send a command that modifies the pub_name of first row
        cmd = cn.CreateCommand
        cmd.CommandText = "UPDATE Publishers SET pub_name ='" & ds.Tables("Publishers").Rows(0)("pub_name").ToString & " (mod)' WHERE Pub_id='" _
            & ds.Tables("Publishers").Rows(0)("pub_id").ToString & "'"
        cmd.ExecuteNonQuery()

        ' modify the local copy of the data.
        With ds.Tables("Publishers")
            ' Modify the first record (this fails because this record has changed)
            .Rows(0)("pub_name") = .Rows(0)("pub_name").ToString & " (new)"
            ' Add a new record (this fails because there is already a record with this key)
            Dim newdr As DataRow = .NewRow
            newdr("pub_id") = "9999"
            newdr("pub_name") = "Vb2TheMax"
            newdr("city") = "Bari"
            newdr("country") = "Italy"
            .Rows.Add(newdr)
            ' delete the second row. (this fails because of RI constraints)
            .Rows(1).Delete()
        End With

        ' Ensure that all conflict are ignored, so that the application
        ' attempts to update all rows.
        AddHandler da.RowUpdating, AddressOf OnRowUpdating
        ' AddHandler da.RowUpdated, AddressOf OnRowUpdated_Continue
        da.ContinueUpdateOnError = True
        da.Update(ds, "Publishers")
        cn.Close()

        ' exit if the attempt was successfull
        If Not ds.HasChanges Then Exit Sub

        ' Not all rows were updated successfully.
        Dim dt As DataTable = ds.Tables("Publishers")

        ' Here's a simple way to evaluate the number of conflicting rows.
        Dim rowCount As Integer = dt.GetChanges().Rows.Count

        ' This block of code shows how to mark conflicting rows
        ' without attempting to resync them with the data source.

        ' Mark all conflicting row with proper error message.
        Dim dr As DataRow
        For Each dr In dt.Rows
            If dr.RowState = DataRowState.Added Then
                dr.RowError = "Failed INSERT operation"
            ElseIf dr.RowState = DataRowState.Modified Then
                dr.RowError = "Failed UPDATE operation"
            ElseIf dr.RowState = DataRowState.Deleted Then
                dr.RowError = "Failed DELETE operation"
                ' Undelete this record, otherwise it wouldn't show in the table.
                dr.RejectChanges()
            End If
        Next
    End Sub

    ' this event fires before a command is sent to the db

    Sub OnRowUpdating(ByVal sender As Object, ByVal args As OleDbRowUpdatingEventArgs)
        ' Exit if this isn't an Update operation.
        If args.StatementType <> StatementType.Update Then Exit Sub

        Dim keyName As String = "pub_id"
        Dim dr As DataRow = args.Row

        Dim i As Integer
        Dim numColumns As Integer = dr.Table.Columns.Count
        Dim setText As String = ""
        Dim whereText As String = ""
        Dim setParams As New ArrayList()
        Dim whereParams As New ArrayList()
        Dim param As OleDbParameter

        For i = 0 To dr.Table.Columns.Count - 1
            Dim dc As DataColumn = dr.Table.Columns(i)
            Dim colName As String = dc.ColumnName

            ' Check whether this column must be added to the SET part.
            If dr(i).ToString <> dr(i, DataRowVersion.Original).ToString Then
                ' Add this column to the SET text.
                If setText.Length > 0 Then setText &= ","
                setText &= "[" & colName & "]=?"
                ' Add a corresponding parameter to the SET arraylist.
                param = New OleDbParameter(colName, dc.DataType)
                param.SourceVersion = DataRowVersion.Current
                param.Value = dr(i)
                setParams.Add(param)
            End If

            ' Check whether this column must be added to the WHERE part.
            ' (primary keys are always added to the WHERE part)
            If colName = keyName Or dr(i).ToString <> dr(i, DataRowVersion.Original).ToString Then
                ' The column name is always added to the WHERE part.
                If whereText.Length > 0 Then whereText &= " AND "
                whereText &= "[" & colName & "]=?"
                ' Add a parameter in the corresponding position of the WHERE arraylist.
                param = New OleDbParameter(colName, dc.DataType)
                param.SourceVersion = DataRowVersion.Original
                param.Value = dr(i, DataRowVersion.Original)
                whereParams.Add(param)
            End If
        Next

        ' Assemble the SQL string.
        Dim sql As String = "UPDATE " & dr.Table.TableName & " SET " & setText & " WHERE " & whereText
        ' Create a command on the same connection as the original command.
        Dim cmd As New OleDbCommand(sql, args.Command.Connection)
        ' Enroll the new command in the same transaction as well
        cmd.Transaction = args.Command.Transaction

        ' Assemble the collection of parameters.
        ' First the SET parameters, then then WHERE parameters.
        For Each param In setParams
            cmd.Parameters.Add(param)
        Next
        For Each param In whereParams
            cmd.Parameters.Add(param)
        Next

        ' assign the the DataAdapter command.
        args.Command = cmd
    End Sub

End Class